/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Factory.DAO.MySQL;

import DatabaseConnection.DBConnectionFactory;
import Factory.DAO.CartDAO;
import Factory.DAOFactory;
import Factory.Tables.Movie;
import Factory.Tables.Cart;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Patrick
 */
public class MySQLCartDAO extends CartDAO{

    @Override
    public void addCart(Cart obj) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("insert into cart(quantity,idmovie,iduser,ischeckout,cartdate,carttime) values (?,?,?,?,date(now()),time(now())) ");
            pstmt.setInt(1, obj.getQuantity());
            pstmt.setInt(2, obj.getMovieID());
            pstmt.setInt(3, obj.getUserID());
            pstmt.setInt(4, 0);
            
            
            
            pstmt.executeUpdate();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(MySQLCartDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public List<Cart> findCart(int transactionID) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM cart natural join movie where idtransaction = ?");
            pstmt.setInt(1, transactionID);
            ResultSet rs = pstmt.executeQuery();
            List<Cart> result = new ArrayList<>();
            Cart cart;
            Movie movie;
            while(rs.next()){
                cart = new Cart();
                movie = new Movie();
                cart.setCartID(rs.getInt("idcart"));
                cart.setTransactionID(transactionID);
                cart.setQuantity(rs.getInt("quantity"));
                movie.setId(rs.getInt("idmovie"));
                movie.setName(rs.getString("'name'"));
                movie.setDescription(rs.getString("description"));
                movie.setCategoryID(rs.getInt("idcategory"));
                movie.setPrice(rs.getFloat("price"));
                cart.setMovie(movie);
                result.add(cart);
            }
            conn.close();
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLCartDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    @Override
    public Cart findCartByMovieID(int movieID) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM cart where idmovie = ?");
            pstmt.setInt(1, movieID);
            ResultSet rs = pstmt.executeQuery();
            
            Cart cart = new Cart();
                     
                
            cart.setCartID(rs.getInt("idcart"));
            cart.setQuantity(rs.getInt("quantity"));
                
                
                
            
            conn.close();
            return cart;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLCartDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    @Override
    public List<Cart> findCartById(int userID) {
        try {
            // TODO code application logic here
            DBConnectionFactory myFactory = DBConnectionFactory.getInstance(DAOFactory.MYSQL);
            Connection conn = myFactory.getConnection();
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM cart natural join movie where iduser = ? and ischeckout=0");
            pstmt.setInt(1, userID);
            ResultSet rs = pstmt.executeQuery();
            List<Cart> result = new ArrayList<>();
            Cart cart;
            Movie movie;
            while(rs.next()){
                cart = new Cart();
                movie = new Movie();
                cart.setCartID(rs.getInt("idcart"));
                cart.setQuantity(rs.getInt("quantity"));
                cart.setMovieID(rs.getInt("idmovie"));
                cart.setcartDate(rs.getDate("cartdate"));
                cart.setcartTime(rs.getTime("carttime"));
                movie.setId(rs.getInt("idmovie"));
                movie.setMoviePic(rs.getString("moviepic"));
                movie.setName(rs.getString("name"));
                movie.setDescription(rs.getString("description"));
                movie.setCategoryID(rs.getInt("idcategory"));
                movie.setPrice(rs.getFloat("price"));
                cart.setMovie(movie);
                result.add(cart);
            }
            conn.close();
            return result;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLCartDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
    
    
    
    
}
